﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MaxLeaf.Common;
using System.Data.SqlClient;
using MaxLeaf.DB;
using System.Data;


namespace MaxLeaf.DAL.K3
{
    /// <summary>
    /// 物料
    /// </summary>
    public class t_Organization_K3DAL : BaseDAL
    {
        /*
         * exec sp_executesql N'INSERT INTO t_Item (FItemClassID,FParentID,FLevel,FName,FNumber,FShortNumber,FFullNumber,FDetail,UUID,FDeleted) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10)',N'@P1 int,@P2 int,@P3 smallint,@P4 varchar(80),@P5 varchar(80),@P6 varchar(80),@P7 varchar(80),@P8 bit,@P9 varchar(38),@P10 smallint',1,44705,4,'test','9.01.001.000002','000002','9.01.001.000002',1,'2977E78B-D34F-4154-AB00-D30E2AED5450',0
         
         */


        /*
         exec sp_executesql N'INSERT INTO t_Organization (FHelpCode,FShortName,FAddress,FStatus,FRegionID,FTrade,FContact,FPhone,FMobilePhone,FFax,FPostalCode,FEmail,FBank,FAccount,FTaxNum,FIsCreditMgr,FSaleMode,FValueAddRate,FCity,FProvince,FCountry,FHomePage,Fcorperate,FCarryingAOS,FTypeID,FSaleID,FStockIDKeep,FCyID,FSetID,FCIQNumber,FARAccountID,FPreAcctID,FOtherARAcctID,FPayTaxAcctID,FAPAccountID,FPreAPAcctID,FOtherAPAcctID,FfavorPolicy,Fdepartment,Femployee,FlastTradeAmount,FlastRPAmount,FmaxDealAmount,FminForeReceiveRate,FminReserverate,FdebtLevel,FPayCondition,FNameEN,FAddrEn,FCIQCode,FRegion,FManageType,F_102,F_103,F_104,F_105,F_106,F_107,F_108,F_109,FShortNumber,FNumber,FName,FParentID,FItemID) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23,@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39,@P40,@P41,@P42,@P43,@P44,@P45,@P46,@P47,@P48,@P49,@P50,@P51,@P52,@P53,@P54,@P55,@P56,@P57,@P58,@P59,@P60,@P61,@P62,@P63,@P64,@P65)',N'@P1 varchar(20),@P2 varchar(50),@P3 varchar(255),@P4 int,@P5 int,@P6 int,@P7 varchar(50),@P8 varchar(40),@P9 varchar(11),@P10 varchar(40),@P11 varchar(20),@P12 varchar(40),@P13 varchar(255),@P14 varchar(80),@P15 varchar(50),@P16 bit,@P17 int,@P18 float,@P19 varchar(80),@P20 varchar(80),@P21 varchar(80),@P22 varchar(80),@P23 varchar(80),@P24 int,@P25 int,@P26 int,@P27 int,@P28 int,@P29 int,@P30 varchar(20),@P31 int,@P32 int,@P33 int,@P34 int,@P35 int,@P36 int,@P37 int,@P38 varchar(255),@P39 int,@P40 int,@P41 float,@P42 float,@P43 float,@P44 float,@P45 float,@P46 int,@P47 int,@P48 varchar(255),@P49 varchar(255),@P50 varchar(255),@P51 int,@P52 int,@P53 varchar(10),@P54 varchar(10),@P55 varchar(50),@P56 varchar(10),@P57 varchar(10),@P58 varchar(10),@P59 varchar(10),@P60 varchar(100),@P61 varchar(80),@P62 varchar(80),@P63 varchar(80),@P64 int,@P65 int',NULL,NULL,NULL,1072,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,1057,17,NULL,NULL,NULL,NULL,NULL,0,40085,0,0,0,0,NULL,0,0,0,0,0,0,0,NULL,0,0,0,0,0,1,1,0,0,NULL,NULL,NULL,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'000002','9.01.001.000002','test',44705,45487
         * 
         * Delete from Access_t_Organization where FItemID=45487
 Insert into Access_t_Organization(FItemID,FParentIDX,FDataAccessView,FDataAccessEdit,FDataAccessDelete)
 Values(45487,44705,convert(varbinary(7200),REPLICATE(char(255),100)),convert(varbinary(7200),REPLICATE(char(255),100)),convert(varbinary(7200),REPLICATE(char(255),100)))
         */
        public t_Organization_K3DAL()
            : base()
        {
            strTableName = "t_Organization";
            strFieldNames = @"FAddress,FCity,FProvince,FCountry,FPostalCode,FPhone,FFax,FEmail,FHomePage,FCreditLimit,FTaxID,FBank,FAccount,FBankNumber,FBrNo,FBoundAttr,FErpClsID,FShortName,FPriorityID,FPOGroupID,FStatus,FLanguageID,FRegionID,FTrade,FMinPOValue,FMaxDebitDate,FLegalPerson,FContact,FContactAcct,FPhoneAcct,FFaxAcct,FZipAcct,FEmailAcct,FAddrAcct,FTax,FCyID,FSetID,FSetDLineID,FTaxNum,FPriceClsID,FOperID,FCIQNumber,FDeleted,FSaleMode,FName,FNumber,FParentID,FShortNumber,FARAccountID,FAPAccountID,FpreAcctID,FlastTradeAmount,FlastRPAmount,FfavorPolicy,Fdepartment,Femployee,Fcorperate,FbeginTradeDate,FendTradeDate,FlastTradeDate,FlastReceiveDate,FcashDiscount,FcurrencyID,FmaxDealAmount,FminForeReceiveRate,FminReserverate,FdebtLevel,FCarryingAOS,FIsCreditMgr,FCreditPeriod,FCreditLevel,FPayTaxAcctID,FValueAddRate,FTypeID,FCreditDays,FCreditAmount,FStockIDAssign,FStockIDInst,FStockIDKeep,FPaperPeriod,FAlarmPeriod,FLicAndPermit,FOtherARAcctID,FOtherAPAcctID,FPreAPAcctID,FSaleID,FHelpCode,FNameEN,FAddrEn,FCIQCode,FRegion,FMobilePhone,FPayCondition,FManageType,FClass,FValue,FRegUserID,FLastModifyDate,FRecentContactDate,FRegDate,FFlat,FClassTypeID,F_102,F_103,F_104,F_105,F_106,F_107,F_108,F_109,F_110";

        }
         
        public override void Update(DBAccess dbOrgCon, DBAccess dbCon, string strKeyValue)
        {
            
            base.Update(dbOrgCon, dbCon, strKeyValue);

        }

        protected override void Update_Before(DBAccess dbOrgCon, DBAccess dbCon, DataRow drOrgData, DataRow drData)
        {
            string strSql = string.Empty;
            KeyValuePair<string, IDataParameter[]> kvp = new KeyValuePair<string, IDataParameter[]>();

            strSql = "Update t_Organization Set FParentID =  @FParentID  Where  FItemID=@FItemID";
            kvp = dbCon.CreateCondParam(new Dictionary<string, object>() { { "FParentID", drData["FParentID"] }, { "FItemID", drData["FItemID"] } });
            dbCon.ExecuteSql(strSql, kvp.Value);

            strSql = "Update t_Item Set FParentID =  @FParentID ,FDeleted=@FDeleted,Fnumber=@Fnumber,FName=@FName,FShortNumber=@FShortNumber,FFullNumber=@Fnumber Where  FItemID=@FItemID";
            kvp = dbCon.CreateCondParam(new Dictionary<string, object>() { { "FParentID", drData["FParentID"] }, { "FDeleted", drData["FDeleted"] }, { "Fnumber", drData["Fnumber"] }, { "FName", drData["FName"] }, { "FShortNumber", drData["FShortNumber"] }, { "FItemID", drData["FItemID"] } });
            dbCon.ExecuteSql(strSql, kvp.Value);
        }
        


        protected override void DAL_GetFieldData(DBAccess dbOrgCon, DBAccess dbDestCon, string strFieldName, DataRow drOrgData, DataRow drDestData)
        {
            if (strFieldName.Equals("FParentID", StringComparison.InvariantCultureIgnoreCase))
            {
                //根据fnumber得到上级组                
                string[] arrFnumber = CvtFunc.ToString(drOrgData["Fnumber"]).Split('.');
                if (arrFnumber.Length > 1)
                {
                    drDestData[strFieldName] = GetFParentID("1", dbOrgCon, dbDestCon, arrFnumber);
                    
                }
                else
                {
                    drDestData[strFieldName] = "0";
                };
            }
            else if (strFieldName.Equals("FItemID", StringComparison.InvariantCultureIgnoreCase))
            {

                string[] arrFnumber = CvtFunc.ToString(drOrgData["Fnumber"]).Split('.');
                string strFParentID = CvtFunc.ToString(drDestData["FParentID"]);
                if (strFParentID.Length == 0)
                {
                    //先得到上级组
                    DAL_GetFieldData(dbOrgCon, dbDestCon, "FParentID", drOrgData, drDestData);
                    strFParentID = CvtFunc.ToString(drDestData["FParentID"]);
                }

                //再fitemid
                drDestData[strFieldName] = GetFItemID(dbOrgCon, dbDestCon, CvtFunc.ToString(drOrgData["FParentID"]), strFParentID, arrFnumber);


            }
            
            else if (strFieldName.Equals("FTypeID", StringComparison.InvariantCultureIgnoreCase))
            {

                //客户类别
                drDestData[strFieldName] = GetFTypeID(dbOrgCon, dbDestCon, CvtFunc.ToString(drOrgData[strFieldName]));


            }
            else if (strFieldName.EndsWith("AcctID", StringComparison.InvariantCultureIgnoreCase) || strFieldName.EndsWith("AccountID", StringComparison.InvariantCultureIgnoreCase))
            {


                drDestData[strFieldName] = GetAcctID(dbOrgCon, dbDestCon, CvtFunc.ToString(drOrgData[strFieldName]));


            }
            else if (strFieldName.Equals("Fdepartment", StringComparison.InvariantCultureIgnoreCase))
            {

                //department
                drDestData[strFieldName] = GetFdepartment(dbOrgCon, dbDestCon, CvtFunc.ToString(drOrgData[strFieldName]));


            }
            else if (strFieldName.Equals("Femployee", StringComparison.InvariantCultureIgnoreCase))
            {

                //Femployee
                drDestData[strFieldName] = GetFemployee(dbOrgCon, dbDestCon, CvtFunc.ToString(drOrgData[strFieldName]));


            }
            else
            {
                base.DAL_GetFieldData(dbOrgCon, dbDestCon, strFieldName, drOrgData, drDestData);
            }
        }

        private string GetFemployee(DBAccess dbOrgCon, DBAccess dbDestCon, string strOrgAcctId)
        {
            if (strOrgAcctId.Length == 0 || strOrgAcctId.Equals("0"))
            {
                return "0";
            }
            t_Base_EmpDAL dal = new t_Base_EmpDAL();
            return dal.GetDestMapKeyValue(dbOrgCon, dbDestCon, strOrgAcctId);
        }

        private string GetFdepartment(DBAccess dbOrgCon, DBAccess dbDestCon, string strOrgAcctId)
        {
            if (strOrgAcctId.Length == 0 || strOrgAcctId.Equals("0"))
            {
                return "0";
            }
            t_departmentDAL dal = new t_departmentDAL();
            return dal.GetDestMapKeyValue(dbOrgCon, dbDestCon, strOrgAcctId);
        }

        private string GetAcctID(DBAccess dbOrgCon, DBAccess dbDestCon, string strOrgAcctId)
        {
            if (strOrgAcctId.Length == 0 || strOrgAcctId.Equals("0"))
            {
                return "0";
            }
            t_AccountDAL dal = new t_AccountDAL();
            return dal.GetDestMapKeyValue(dbOrgCon, dbDestCon, strOrgAcctId);
        }
        
        private string GetFTypeID(DBAccess dbOrgCon, DBAccess dbDestCon, string strOrgFInterID)
        {
            t_SubMessageDAL dal = new t_SubMessageDAL();
            return dal.GetFInterID(dbOrgCon, dbDestCon, strOrgFInterID);
        }

       

        private string GetFParentID(string strFItemClassID, DBAccess dbOrgCon, DBAccess dbDestCon, string[] arrFnumber)
        {

            const int intFDetail = 0;
            string strFItemId = string.Empty;
            t_ItemDAL dal = new t_ItemDAL();
            for (int i = 0; i < arrFnumber.Length - 1; i++)
            {
                string strFParentID = "0";
                if (i != 0)
                {
                    strFParentID = strFItemId;
                }
                //得到每级的FItemId
                strFItemId = dal.GetFItemId(dbOrgCon, dbDestCon, strFItemClassID, i + 1, string.Empty, strFParentID, string.Join(".", arrFnumber, 0, i + 1), intFDetail);
                if (strFItemClassID.Equals("1"))
                {
                    UpdateAccess_t_Organization(dbDestCon, strFItemId, strFParentID);
                }
                


            }

            return strFItemId;
        }

        private void UpdateAccess_t_Organization(DBAccess dbDestCon, string strFItemId, string strFParentID)
        {
            /*
         exec sp_executesql N'INSERT INTO t_Organization (FHelpCode,FShortName,FAddress,FStatus,FRegionID,FTrade,FContact,FPhone,FMobilePhone,FFax,FPostalCode,FEmail,FBank,FAccount,FTaxNum,FIsCreditMgr,FSaleMode,FValueAddRate,FCity,FProvince,FCountry,FHomePage,Fcorperate,FCarryingAOS,FTypeID,FSaleID,FStockIDKeep,FCyID,FSetID,FCIQNumber,FARAccountID,FPreAcctID,FOtherARAcctID,FPayTaxAcctID,FAPAccountID,FPreAPAcctID,FOtherAPAcctID,FfavorPolicy,Fdepartment,Femployee,FlastTradeAmount,FlastRPAmount,FmaxDealAmount,FminForeReceiveRate,FminReserverate,FdebtLevel,FPayCondition,FNameEN,FAddrEn,FCIQCode,FRegion,FManageType,F_102,F_103,F_104,F_105,F_106,F_107,F_108,F_109,FShortNumber,FNumber,FName,FParentID,FItemID) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23,@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39,@P40,@P41,@P42,@P43,@P44,@P45,@P46,@P47,@P48,@P49,@P50,@P51,@P52,@P53,@P54,@P55,@P56,@P57,@P58,@P59,@P60,@P61,@P62,@P63,@P64,@P65)',N'@P1 varchar(20),@P2 varchar(50),@P3 varchar(255),@P4 int,@P5 int,@P6 int,@P7 varchar(50),@P8 varchar(40),@P9 varchar(11),@P10 varchar(40),@P11 varchar(20),@P12 varchar(40),@P13 varchar(255),@P14 varchar(80),@P15 varchar(50),@P16 bit,@P17 int,@P18 float,@P19 varchar(80),@P20 varchar(80),@P21 varchar(80),@P22 varchar(80),@P23 varchar(80),@P24 int,@P25 int,@P26 int,@P27 int,@P28 int,@P29 int,@P30 varchar(20),@P31 int,@P32 int,@P33 int,@P34 int,@P35 int,@P36 int,@P37 int,@P38 varchar(255),@P39 int,@P40 int,@P41 float,@P42 float,@P43 float,@P44 float,@P45 float,@P46 int,@P47 int,@P48 varchar(255),@P49 varchar(255),@P50 varchar(255),@P51 int,@P52 int,@P53 varchar(10),@P54 varchar(10),@P55 varchar(50),@P56 varchar(10),@P57 varchar(10),@P58 varchar(10),@P59 varchar(10),@P60 varchar(100),@P61 varchar(80),@P62 varchar(80),@P63 varchar(80),@P64 int,@P65 int',NULL,NULL,NULL,1072,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,1057,17,NULL,NULL,NULL,NULL,NULL,0,40085,0,0,0,0,NULL,0,0,0,0,0,0,0,NULL,0,0,0,0,0,1,1,0,0,NULL,NULL,NULL,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'000002','9.01.001.000002','test',44705,45487
         * 
         * Delete from Access_t_Organization where FItemID=45487
 Insert into Access_t_Organization(FItemID,FParentIDX,FDataAccessView,FDataAccessEdit,FDataAccessDelete)
 Values(45487,44705,convert(varbinary(7200),REPLICATE(char(255),100)),convert(varbinary(7200),REPLICATE(char(255),100)),convert(varbinary(7200),REPLICATE(char(255),100)))
         */
            string strSql = @"
                if not exists(select * from Access_t_Organization where FItemID=@FItemID)
                begin
                 Insert into Access_t_Organization(FItemID,FParentIDX,FDataAccessView,FDataAccessEdit,FDataAccessDelete)
                 Values(@FItemID,@FParentIDX,convert(varbinary(7200),REPLICATE(char(255),100)),convert(varbinary(7200),REPLICATE(char(255),100)),convert(varbinary(7200),REPLICATE(char(255),100)))

                end
                ";
            dbDestCon.ExecuteSql(strSql, new SqlParameter[] { new SqlParameter("@FItemID", strFItemId), new SqlParameter("@FParentIDX", strFParentID) });
        }

        
        private string GetFItemID(DBAccess dbOrgCon, DBAccess dbDestCon, string strOrgFParentID, string strFParentID, string[] arrFnumber)
        {
            const string strFItemClassID = "1";
            const int intFDetail = 1;
            string strFItemId = string.Empty;
            t_ItemDAL dal = new t_ItemDAL();


            strFItemId = dal.GetFItemId(dbOrgCon, dbDestCon, strFItemClassID, arrFnumber.Length, strOrgFParentID, strFParentID, string.Join(".", arrFnumber, 0, arrFnumber.Length), intFDetail);

            UpdateAccess_t_Organization(dbDestCon, strFItemId, strFParentID);


            return strFItemId;
        }

        public override string GetDestMapKeyValue(DBAccess dbOrgCon, DBAccess dbDestCon, string strOrgFItemID)
        {
            if (dbDestCon.ServerMasterDB)
            {
                return strOrgFItemID;
            }

            KeyValuePair<string, IDataParameter[]> kvp = dbDestCon.CreateCondParam(new Dictionary<string, object>() { { "TableName", "t_item" }, { "OrgKeyValue", strOrgFItemID } });
            string strSql = string.Format("select DestKeyValue from mkeymap where {0}", kvp.Key);

            string strFitemid = dbDestCon.GetValue(strSql, kvp.Value);
            if (strFitemid.Length > 0)
            {
                return strFitemid;
            }

            Update(dbOrgCon, dbDestCon, strOrgFItemID);

            kvp = dbDestCon.CreateCondParam(new Dictionary<string, object>() { { "TableName", "t_item" }, { "OrgKeyValue", strOrgFItemID } });
            return dbDestCon.GetValue(strSql, kvp.Value);
        }
    }
}